package com.tuanzi.loan;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

import org.activiti.engine.RepositoryService;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.RecordMapper;
import org.jooq.conf.ParamType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationListener;
import org.springframework.context.event.ContextRefreshedEvent;
import org.springframework.stereotype.Component;

import com.base.common.log.LogService;
import com.google.common.collect.Lists;
import com.google.common.collect.Queues;
import com.google.common.collect.Sets;
import com.google.gson.*;
import com.jumai.server.qiniu.QiniuUploadUtils;
import com.jumai.server.test.TestUtils;
import com.starbaba.model.network.Phead;
import com.tuanzi.loan.business.analyzer.BaiduJRAnalyzer;
import com.tuanzi.loan.business.analyzer.DianJinAnalyzer;
import com.tuanzi.loan.business.analyzer.InstallAppAnalyzer;
import com.tuanzi.loan.business.entity.family.UserLabel;
import com.tuanzi.loan.business.entity.sms.SmsCalculateTask;
import com.tuanzi.loan.business.entity.sms.SmsCalculateTaskStatus;
import com.tuanzi.loan.business.entity.sms.SmsSendTask;
import com.tuanzi.loan.business.entity.sms.SmsSendTaskStatus;
import com.tuanzi.loan.business.repository.family.UserLabelRepository;
import com.tuanzi.loan.business.service.family.UserCreditRecordService;
import com.tuanzi.loan.business.service.sms.SmsCalculateTaskService;
import com.tuanzi.loan.business.service.sms.SmsSendTaskService;
import com.tuanzi.loan.core.util.TimeUtils;
import com.tuanzi.loan.generated.jooq.loan.tables.LoanXfUserInfo;
import com.tuanzi.loan.generated.jooq.loan.tables.UserInstallDetail;
import com.tuanzi.loan.workflow.service.WorkflowProcessorService;
import com.tuanzi.server.loan_base.util.BirthdayUtils;
import com.tuanzi.server.loan_base.util.DateUtils;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;

/**
 * @author Ray 2017/09/21
 */
@Component
@Slf4j
public class SystemInitializer implements ApplicationListener<ContextRefreshedEvent> {

	@Autowired
	private SmsCalculateTaskService smsCalculateTaskService;
	@Autowired
	private SmsSendTaskService smsSendTaskService;
	@Autowired
	private DSLContext dslContext;
	@Autowired
	protected RepositoryService repositoryService;
	@Autowired
	private UserLabelRepository userLabelRepository;
	@Autowired
	private WorkflowProcessorService workflowProcessorService;
	@Autowired
	private InstallAppAnalyzer installAppAnalyzer;
	@Autowired
	private BaiduJRAnalyzer baiduJRAnalyzer;
	@Autowired
	private DianJinAnalyzer dianJinAnalyzer;
	@Autowired
	private UserCreditRecordService userCreditRecordService;

	private final LinkedBlockingQueue<Test> queue = Queues.newLinkedBlockingQueue(200000);
	private Map<String, AtomicInteger> userInstallCountMap = new ConcurrentHashMap<>(600000);

	@Override
	public void onApplicationEvent(ContextRefreshedEvent event) {
		if (event.getApplicationContext().getParent() == null) {
			QiniuUploadUtils.init();
			resetSmsTask();
		}
		//
		// AtomicInteger baiduABC = new AtomicInteger(0);
		// AtomicInteger dianjinzhuce = new AtomicInteger(0);
		// AtomicInteger dianjinhuankuan = new AtomicInteger(0);
		// AtomicInteger pengyuanpingjunfangkuan1 = new AtomicInteger(0);
		// AtomicInteger pengyuanpingjunfangkuan2 = new AtomicInteger(0);
		// AtomicInteger pengyuanpingjunfangkuan3 = new AtomicInteger(0);
		// AtomicInteger pengyuanpingjunfangkuan4 = new AtomicInteger(0);
		// AtomicInteger mingzhongyunyingshang = new AtomicInteger(0);
		// AtomicInteger pengyuanyuqibishu1 = new AtomicInteger(0);
		// AtomicInteger pengyuanyuqibishu2 = new AtomicInteger(0);
		// AtomicInteger pengyuanyuqibishu3 = new AtomicInteger(0);
		// AtomicInteger notmatch = new AtomicInteger(0);

		// Runnable newRunnable = () -> {
		// while (true) {
		// try {
		// Test test = queue.take();
		// Long userId = test.getUserId();
		// boolean notMatch = true;
		//
		// List<UserCreditRecord> userCreditRecords =
		// userCreditRecordService.findAllByUserId(userId);
		//
		// if (CollectionUtils.isEmpty(userCreditRecords)) {
		// continue;
		// }
		//
		// for (UserCreditRecord userCreditRecord : userCreditRecords) {
		// switch (userCreditRecord.getSourceType()) {
		// case BAIDU_JR:
		// if (StringUtils.isBlank(userCreditRecord.getText1())) {
		// break;
		// }
		// String blackLevel =
		// baiduJRAnalyzer.getBlackLevel(userCreditRecord.getText1());
		// if (StringUtils.equalsIgnoreCase(blackLevel, "A") ||
		// StringUtils.equalsIgnoreCase(blackLevel, "B") ||
		// StringUtils.equalsIgnoreCase(blackLevel, "C")
		// || StringUtils.equalsIgnoreCase(blackLevel, "D")) {
		// baiduABC.incrementAndGet();
		// notMatch = false;
		// }
		// break;
		// case DIAN_JIN:
		// if (StringUtils.isBlank(userCreditRecord.getText1())) {
		// break;
		// }
		// if
		// (dianJinAnalyzer.analyze(userCreditRecord.getText1()).getResults().stream().filter(y
		// -> "EMR002".equalsIgnoreCase(y.getOriginalType()))
		// .map(UserValidateView.DianJinResult::getPlatformCode).distinct().count() >=
		// 3) {
		// dianjinzhuce.incrementAndGet();
		// notMatch = false;
		// }
		//
		// if
		// (dianJinAnalyzer.analyze(userCreditRecord.getText1()).getResults().stream()
		// .filter(y -> "EMR012".equalsIgnoreCase(y.getOriginalType()) ||
		// "EMR013".equalsIgnoreCase(y.getOriginalType()))
		// .map(UserValidateView.DianJinResult::getPlatformCode).distinct().count() >=
		// 1) {
		// dianjinhuankuan.incrementAndGet();
		// notMatch = false;
		// }
		// break;
		// case PENGYUAN: {
		// if (StringUtils.isBlank(userCreditRecord.getText1())) {
		// break;
		// }
		// org.dom4j.Document document =
		// DocumentHelper.parseText(userCreditRecord.getText1());
		// List<Element> elements0 = document.getRootElement().elements();
		// if (CollectionUtils.isNotEmpty(elements0) && elements0.size() == 1) {
		// List<Element> elements = elements0.get(0).elements();
		// for (Element element : elements) {
		// if ("creditBehaviorInfo".equalsIgnoreCase(element.getName())) {
		// List<Element> elements1 = element.elements();
		// for (Element element1 : elements1) {
		// if ("avgCredits".equalsIgnoreCase(element1.getName())) {
		// String value = element1.getStringValue();
		// if (value != null) {
		// Long aaa = NumberUtils.toLong(value);
		// if (aaa >= 1000 && aaa < 3000) {
		// pengyuanpingjunfangkuan1.incrementAndGet();
		// notMatch = false;
		// } else if (aaa >= 3000 && aaa < 5000) {
		// pengyuanpingjunfangkuan2.incrementAndGet();
		// notMatch = false;
		// } else if (aaa >= 5000 && aaa < 10000) {
		// pengyuanpingjunfangkuan3.incrementAndGet();
		// notMatch = false;
		// } else if (aaa > 10000) {
		// pengyuanpingjunfangkuan4.incrementAndGet();
		// notMatch = false;
		// }
		// }
		// }
		// }
		// }
		//
		// if ("overdueLoanInfo".equalsIgnoreCase(element.getName())) {
		// List<Element> elements1 = element.elements();
		// for (Element element1 : elements1) {
		// if ("overdueStat".equalsIgnoreCase(element1.getName())) {
		// List<Element> elements2 = element1.elements();
		// for (Element element2 : elements2) {
		// if ("overdueTotal".equalsIgnoreCase(element2.getName())) {
		// String value = element2.getStringValue();
		// if (value != null) {
		// Long aaa = NumberUtils.toLong(value);
		// if (aaa > 5) {
		// pengyuanyuqibishu1.incrementAndGet();
		// notMatch = false;
		// } else if (aaa <= 5 && aaa > 1) {
		// pengyuanyuqibishu2.incrementAndGet();
		// notMatch = false;
		// } else if (aaa <= 1) {
		// pengyuanyuqibishu3.incrementAndGet();
		// notMatch = false;
		// }
		// }
		// }
		// }
		//
		// }
		// }
		// }
		// }
		// }
		// }
		// break;
		// case MOXIE: {
		// if (StringUtils.isBlank(userCreditRecord.getText5())) {
		// break;
		// }
		// MoZhang moZhang = GsonUtils.toObject(userCreditRecord.getText5(),
		// MoZhang.class);
		// if (moZhang != null && moZhang.getBlack_info_detail() != null &&
		// CollectionUtils.isEmpty(moZhang.getBlack_info_detail().getBlack_types())) {
		// mingzhongyunyingshang.incrementAndGet();
		// }
		// }
		// break;
		// }
		// }
		//
		// if (notMatch) {
		// notmatch.incrementAndGet();
		// }
		// } catch (Exception e) {
		// e.printStackTrace();
		// }
		// }
		// };
		//
		// IntStream.range(0, 40).forEach(x -> new Thread(newRunnable).start());
		//
		// List<Test> tests = dslContext
		// .fetch("SELECT distinct(user_id) as user_id FROM pay_order as b WHERE
		// b.order_status = 2 and b.create_time >= '2018-04-01' AND b.create_time <=
		// '2018-04-10 23:59:59'")
		// .map(new RecordMapper<Record, Test>() {
		// @Override
		// public Test map(Record record) {
		// Test test = new Test();
		// test.setUserId(record.get("user_id", Long.class));
		// return test;
		// }
		// });
		//
		// for (Test test : tests) {
		// try {
		// queue.put(test);
		// } catch (Exception e) {
		// e.printStackTrace();
		// }
		// }
		//
		// while (queue.size() != 0) {
		// System.out.println("waiting....");
		// try {
		// TimeUnit.SECONDS.sleep(3);
		// } catch (Exception e) {
		// e.printStackTrace();
		// }
		// }
		// log.error("百度黑名单" + baiduABC.get());
		// log.error("点金注册" + dianjinzhuce.get());
		// log.error("点金逾期还款" + dianjinhuankuan.get());
		// log.error("鹏远放款1" + pengyuanpingjunfangkuan1.get());
		// log.error("鹏远放款2" + pengyuanpingjunfangkuan2.get());
		// log.error("鹏远放款3" + pengyuanpingjunfangkuan3.get());
		// log.error("鹏远放款4" + pengyuanpingjunfangkuan4.get());
		// log.error("运营商" + mingzhongyunyingshang.get());
		// log.error("鹏远笔数1" + pengyuanyuqibishu1.get());
		// log.error("鹏远笔数2" + pengyuanyuqibishu2.get());
		// log.error("鹏远笔数3" + pengyuanyuqibishu3.get());
		// log.error("未命中" + notmatch.get());
	}

	private void resetSmsTask() {
		SmsCalculateTask smsCalculateTask = smsCalculateTaskService.findTopOneByStatus(SmsCalculateTaskStatus.CALCULATING);
		if (smsCalculateTask != null) {
			smsCalculateTask.setStatus(SmsCalculateTaskStatus.READY_CALCULATE);
			smsCalculateTaskService.saveOrUpdate(smsCalculateTask);
		}

//		SmsSendTask smsSendTask = smsSendTaskService.findTopOneByStatus(SmsSendTaskStatus.SENDING);
//		if (smsSendTask != null) {
//			smsSendTask.setStatus(SmsSendTaskStatus.READY_SEND);
//			smsSendTaskService.saveOrUpdate(smsSendTask);
//		}
	}

	private void test11() throws IOException {

		List<Test> tests = dslContext.fetch(
				"select b.idcard,b.sex from comm_user as a inner join loan_user_info as b on a.id = b.userid where a.createtime >= '2017-12-31' and a.createtime <='2017-12-31 23:59:59'")
				.map(new RecordMapper<Record, Test>() {
					@Override
					public Test map(Record record) {
						Test test = new Test();
						test.setIdcard(record.get("idcard", String.class));
						test.setSex(record.get("sex", String.class));
						return test;
					}
				});

		Integer age1 = 0;
		Integer age2 = 0;
		Integer age3 = 0;
		Integer age4 = 0;
		Integer age5 = 0;
		Integer age6 = 0;
		Integer age7 = 0;
		Integer man = 0;
		Integer lady = 0;

		for (Test test : tests) {
			try {
				Integer age = BirthdayUtils.calculateAgeFromIdCard(test.getIdcard());
				if (age < 18) {
					age1++;
				} else if (age >= 18 && age <= 23) {
					age2++;
				} else if (age >= 24 && age <= 27) {
					age3++;
				} else if (age >= 28 && age <= 34) {
					age4++;
				} else if (age >= 35 && age <= 40) {
					age5++;
				} else if (age >= 41 && age <= 55) {
					age6++;
				} else if (age > 55) {
					age7++;
				}

				if (StringUtils.isNotBlank(test.getSex())) {
					if ("先生".equalsIgnoreCase(test.getSex())) {
						man++;
					}

					if ("女士".equalsIgnoreCase(test.getSex())) {
						lady++;
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		log.error("age1" + age1);
		log.error("age2" + age2);
		log.error("age3" + age3);
		log.error("age4" + age4);
		log.error("age5" + age5);
		log.error("age6" + age6);
		log.error("age7" + age7);
		log.error("man" + man);
		log.error("lady" + lady);

	}

	private void ai() throws IOException {
		// Runnable newRunnable = () -> {
		// while (true) {
		// try {
		// Test test = queue.take();
		//
		//
		// log.info("hahhaha : {}", hahaha.stream().collect(Collectors.joining(",")));
		// } catch (Exception e) {
		// e.printStackTrace();
		// }
		// }
		// };
		//
		// IntStream.range(0, 20).forEach(x -> new Thread(newRunnable).start());

		List<Test> tests = dslContext.fetch(
				"SELECT a.id as user_id , a.phoneid as phoneid , b.sex, idcard,province,amount,income,credit,sesame_point,a.createtime FROM comm_user AS a INNER JOIN loan_user_info AS b ON a.id = b.userid  WHERE a.id IN ( SELECT c.user_id FROM pay_order as c WHERE c.create_time >= '2018-02-28' AND c.credit_type = '22' AND c.order_status = 2 )")
				.map(new RecordMapper<Record, Test>() {
					@Override
					public Test map(Record record) {
						Test test = new Test();
						test.setPhoneId(record.get("phoneid", String.class));
						test.setProvince(record.get("province", String.class));
						test.setUserId(record.get("user_id", Long.class));
						test.setSex(record.get("sex", String.class));
						test.setIdcard(record.get("idcard", String.class));
						test.setCredit(record.get("credit", String.class));
						test.setIncome(record.get("income", Integer.class));
						test.setAmount(record.get("amount", Integer.class));
						test.setCreateTime(record.get("createtime", Date.class));
						// test.setApplyTime(record.get("create_time", Date.class));
						return test;
					}
				});

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("征信用户");
		HSSFRow head = sheet.createRow(0);
		HSSFCellStyle style = wb.createCellStyle();

		HSSFCell cell1 = head.createCell(0);
		cell1.setCellValue("省份");

		HSSFCell cell3 = head.createCell(1);
		cell3.setCellValue("性别");

		HSSFCell cell4 = head.createCell(2);
		cell4.setCellValue("年龄");

		HSSFCell cell5 = head.createCell(3);
		cell5.setCellValue("有无信用卡");

		HSSFCell cell6 = head.createCell(4);
		cell6.setCellValue("收入");

		HSSFCell cell7 = head.createCell(5);
		cell7.setCellValue("贷款意向金额");

		HSSFCell cell10 = head.createCell(6);
		cell10.setCellValue("用户注册时间");

		HSSFCell cell11 = head.createCell(7);
		cell11.setCellValue("用户提交订单时间");

		HSSFCell cell12 = head.createCell(8);
		cell12.setCellValue("用户支付时间");

		HSSFCell cell13 = head.createCell(9);
		cell13.setCellValue("注册>提交订单时间");

		HSSFCell cell14 = head.createCell(10);
		cell14.setCellValue("提单>支付时间");

		HSSFCell cell15 = head.createCell(11);
		cell15.setCellValue("用户类型");

		int index = 1;
		for (Test test : tests) {
			try {

				Record record = dslContext.fetchOne("select result from loan_pdl_result where phone_id = '" + test.getPhoneId() + "' order by time desc limit 1");

				Integer lastestUserType = null;
				try {
					lastestUserType = record.get("result", Integer.class);
				} catch (Exception e) {

				}

				String applyDateStr = dslContext.fetchOne("select create_time from pay_order where user_id = '" + test.getUserId() + "' order by create_time asc limit 1")
						.into(String.class);

				String payDateStr = dslContext
						.fetchOne("select create_time from pay_order where user_id = '" + test.getUserId() + "' and order_status = 2 order by create_time asc limit 1")
						.into(String.class);

				HSSFRow row = sheet.createRow(index);
				row.createCell(0).setCellValue(StringUtils.isBlank(test.getProvince()) ? "" : test.getProvince());
				row.createCell(1).setCellValue(test.getSex() == null ? "" : test.getSex());
				row.createCell(2).setCellValue(BirthdayUtils.calculateAgeFromIdCard(test.getIdcard()) + "");
				row.createCell(3).setCellValue(test.getCredit() == null ? "" : test.getCredit());
				row.createCell(4).setCellValue(test.getIncome() + "");
				row.createCell(5).setCellValue(test.getAmount() + "");
				row.createCell(6).setCellValue(DateUtils.formatDateToYyyyMMddHHmmss(test.getCreateTime()));
				row.createCell(7).setCellValue(applyDateStr);
				row.createCell(8).setCellValue(payDateStr);
				row.createCell(9).setCellValue(TimeUtils.formatDuring(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(applyDateStr).getTime() - test.getCreateTime().getTime()));
				row.createCell(10).setCellValue(TimeUtils.formatDuring(
						new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(payDateStr).getTime() - new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(applyDateStr).getTime()));
				row.createCell(11).setCellValue(lastestUserType == null ? "" : lastestUserType + "");
				System.out.println("ok");
				index++;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		try (FileOutputStream a = new FileOutputStream("/Users/Ray/Documents/黑卡(未支付).xlsx")) {
			wb.write(a);
		}

		// while (queue.size() != 0) {
		// System.out.println("waiting....");
		// try {
		// TimeUnit.SECONDS.sleep(3);
		// } catch (Exception e) {
		// e.printStackTrace();
		// }
		// }
	}

	private void caonima() throws IOException {
		// Runnable newRunnable = () -> {
		// while (true) {
		// try {
		// Test test = queue.take();
		//
		//
		// log.info("hahhaha : {}", hahaha.stream().collect(Collectors.joining(",")));
		// } catch (Exception e) {
		// e.printStackTrace();
		// }
		// }
		// };
		//
		// IntStream.range(0, 20).forEach(x -> new Thread(newRunnable).start());

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("银河黑卡未支付用户");
		HSSFRow head = sheet.createRow(0);
		HSSFCellStyle style = wb.createCellStyle();

		HSSFCell cell1 = head.createCell(0);
		cell1.setCellValue("年龄");

		HSSFCell cell2 = head.createCell(1);
		cell2.setCellValue("芝麻分");

		HSSFCell cell3 = head.createCell(2);
		cell3.setCellValue("地区");

		List<String> mobs = IOUtils.readLines(new FileInputStream(new File("/Users/Ray/Documents/3月20.csv")), StandardCharsets.UTF_8);

		int index = 1;
		Integer total = 0;
		for (String mob : mobs) {
			try {
				String[] aaaa = StringUtils.split(mob, ",");
				HSSFRow row = sheet.createRow(index);
				row.createCell(0).setCellValue(BirthdayUtils.calculateAgeFromIdCard(aaaa[0]));
				row.createCell(1).setCellValue(aaaa[1]);
				row.createCell(2).setCellValue(aaaa[2]);
				index++;
			} catch (Exception e) {
				e.printStackTrace();
				total++;
			}
		}
		log.error("error count : {} ", total);

		try (FileOutputStream a = new FileOutputStream("/Users/Ray/Documents/3月20.xlsx")) {
			wb.write(a);
		}

		// while (queue.size() != 0) {
		// System.out.println("waiting....");
		// try {
		// TimeUnit.SECONDS.sleep(3);
		// } catch (Exception e) {
		// e.printStackTrace();
		// }
		// }
	}

	private void fenleiPDL() {

		Runnable newRunnable = () -> {
			while (true) {
				try {
					Test test = queue.take();
					List<String> packagesrc = dslContext.fetch("select packagesrc from user_install where phoneid='" + test.getPhoneId() + "'").into(String.class);
					Integer count = countAppIntersection(packagesrc.parallelStream().collect(Collectors.toSet()));
					dslContext.insertInto(UserInstallDetail.USER_INSTALL_DETAIL)
							.columns(UserInstallDetail.USER_INSTALL_DETAIL.PHONE_ID, UserInstallDetail.USER_INSTALL_DETAIL.LOAN_APP_COUNT).values(test.getPhoneId(), count)
							.execute();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		};

		IntStream.range(0, 20).forEach(x -> new Thread(newRunnable).start());

		List<Test> tests = dslContext.fetch("select id,phoneid from comm_user where createtime >= '2017-12-25'").map(new RecordMapper<Record, Test>() {
			@Override
			public Test map(Record record) {
				Test test = new Test();
				test.setUserId(record.get("id", Long.class));
				test.setPhoneId(record.get("phoneid", String.class));
				return test;
			}
		});

		for (Test test : tests) {
			try {
				queue.put(test);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		while (queue.size() != 0) {
			System.out.println("waiting....");
			try {
				TimeUnit.SECONDS.sleep(3);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private void fillUserInstallDetail() {

		Runnable newRunnable = () -> {
			while (true) {
				try {
					Test test = queue.take();
					if (StringUtils.isBlank(test.getPhoneId())) {
						continue;
					}
					Long totalCount = dslContext.fetchOne("SELECT COUNT(1) FROM user_install_detail where phone_id = '" + test.getPhoneId() + "'").into(Long.class);
					if (totalCount <= 0) {
						List<String> packagesrc = dslContext.fetch("select packagesrc from user_install where phoneid='" + test.getPhoneId() + "'").into(String.class);
						Integer loanCount = installAppAnalyzer.countLoanAppIntersection(packagesrc.parallelStream().collect(Collectors.toSet()));
						Integer pdlCount = installAppAnalyzer.countPdlAppIntersection(packagesrc.parallelStream().collect(Collectors.toSet()));
						dslContext
								.insertInto(UserInstallDetail.USER_INSTALL_DETAIL).columns(UserInstallDetail.USER_INSTALL_DETAIL.PHONE_ID,
										UserInstallDetail.USER_INSTALL_DETAIL.LOAN_APP_COUNT, UserInstallDetail.USER_INSTALL_DETAIL.PDL_APP_COUNT)
								.values(test.getPhoneId(), loanCount, pdlCount).execute();
						System.out.println("ok");
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		};

		IntStream.range(0, 20).forEach(x -> new Thread(newRunnable).start());

		List<Test> tests = dslContext.fetch("select id,phoneid from comm_user where createtime >= '2017-07-01' order by createtime asc").map(new RecordMapper<Record, Test>() {
			@Override
			public Test map(Record record) {
				Test test = new Test();
				test.setUserId(record.get("id", Long.class));
				test.setPhoneId(record.get("phoneid", String.class));
				return test;
			}
		});

		for (Test test : tests) {
			try {
				queue.put(test);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		while (queue.size() != 0) {
			System.out.println("waiting....");
			try {
				TimeUnit.SECONDS.sleep(3);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private void countLoanCallCount() {

		Runnable newRunnable = () -> {
			while (true) {
				try {
					Test test = queue.take();
					AtomicInteger loanCount = new AtomicInteger();
					if (StringUtils.isNotBlank(test.getPhoneBook())) {

						test.setPhoneBook(StringUtils.replace(test.getPhoneBook(), "\\", ""));
						test.setPhoneBook(StringUtils.replace(test.getPhoneBook(), " ", ""));
						JsonArray array = new JsonParser().parse(test.getPhoneBook()).getAsJsonArray();
						for (JsonElement jsonElement : array) {
							List<String> phones = Lists.newArrayList();
							JsonElement phoneElement = jsonElement.getAsJsonObject().get("phone");
							if (phoneElement.isJsonNull()) {
								continue;
							}

							if (phoneElement.isJsonArray()) {
								for (JsonElement jsonElement1 : phoneElement.getAsJsonArray()) {
									phones.add(jsonElement1.getAsString());
								}
							} else {
								phones.add(phoneElement.getAsString());
							}

							for (String phone : phones) {
								if (loanPhones.contains(phone)) {
									loanCount.incrementAndGet();
								}
							}
						}
					}

					if (StringUtils.isNotBlank(test.getCallRecord())) {

						test.setCallRecord(StringUtils.replace(test.getCallRecord(), "\\", ""));
						test.setCallRecord(StringUtils.replace(test.getCallRecord(), " ", ""));
						JsonArray array = new JsonParser().parse(test.getCallRecord()).getAsJsonArray();

						for (JsonElement jsonElement : array) {
							List<String> phones = Lists.newArrayList();
							JsonElement phoneElement = jsonElement.getAsJsonObject().get("phone");
							if (phoneElement.isJsonNull()) {
								continue;
							}

							if (phoneElement.isJsonArray()) {
								for (JsonElement jsonElement1 : phoneElement.getAsJsonArray()) {
									phones.add(jsonElement1.getAsString());
								}
							} else {
								phones.add(phoneElement.getAsString());
							}

							for (String phone : phones) {
								if (loanPhones.contains(phone)) {
									loanCount.incrementAndGet();
								}
							}
						}
					}

					log.info("generated sql : {};", dslContext.update(LoanXfUserInfo.LOAN_XF_USER_INFO).set(LoanXfUserInfo.LOAN_XF_USER_INFO.LOAN_CALL_COUNT, loanCount.get())
							.where(LoanXfUserInfo.LOAN_XF_USER_INFO.USERID.eq(test.getUserId())).getSQL(ParamType.INLINED));

					// dslContext.update(LoanXfUserInfo.LOAN_XF_USER_INFO).set(LoanXfUserInfo.LOAN_XF_USER_INFO.LOAN_CALL_COUNT,
					// loanCount.get())
					// .where(LoanXfUserInfo.LOAN_XF_USER_INFO.USERID.eq(test.getUserId())).execute();
				} catch (Exception e) {
				}
			}
		};

		IntStream.range(0, 20).forEach(x -> new Thread(newRunnable).start());

		int limit = 5000;
		int index = 0;
		while (true) {
			List<Test> tests = dslContext.fetch("select userid, call_record, phone_book from loan_xf_user_info where loan_call_count is null limit " + index * limit + "," + limit)
					.map(new RecordMapper<Record, Test>() {
						@Override
						public Test map(Record record) {
							Test test = new Test();
							test.setUserId(record.get("userid", Long.class));
							test.setPhoneBook(record.get("phone_book", String.class));
							test.setCallRecord(record.get("call_record", String.class));
							return test;
						}
					});

			if (tests.size() == 0) {
				break;
			}

			index++;

			for (Test test : tests) {
				try {
					queue.put(test);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}

		while (queue.size() != 0) {
			System.out.println("waiting....");
			try {
				TimeUnit.SECONDS.sleep(3);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private void countinSTALLPP() {
		try {
			List<String> a = IOUtils.readLines(new FileInputStream(new File("/Users/Ray/Documents/new/loan-management.log")), StandardCharsets.UTF_8);
			List<String> b = IOUtils.readLines(new FileInputStream(new File("/Users/Ray/Documents/new/loan-management2.log")), StandardCharsets.UTF_8);
			List<String> c = IOUtils.readLines(new FileInputStream(new File("/Users/Ray/Documents/new/loan-management3.log")), StandardCharsets.UTF_8);
			List<String> d = IOUtils.readLines(new FileInputStream(new File("/Users/Ray/Documents/new/loan-management4.log")), StandardCharsets.UTF_8);

			a.addAll(b);
			a.addAll(c);
			a.addAll(d);

			a.stream().map(x -> {
				String[] strs = StringUtils.split(x, ",");
				if (strs.length == 2) {
					Test test = new Test();

					if (NumberUtils.isDigits(strs[1])) {
						test.setUserId(NumberUtils.toLong(strs[1]));
						test.setPackageName(strs[0]);
						return test;
					} else {
						return null;
					}
				}
				return null;
			}).filter(x -> x != null).sorted(Comparator.comparing(Test::getUserId).reversed()).limit(200)
					.forEach(x -> System.out.println(x.getPackageName() + "," + x.getUserId()));

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void calculateUserInstall() {
		Long userInstallCount = dslContext.fetchOne("select count(1) from `user_install`").into(Long.class);

		Runnable newRunnable = () -> {
			while (true) {
				try {
					Test test = queue.take();
					AtomicInteger atomicInteger = userInstallCountMap.get(test.getPackageName());
					if (atomicInteger == null) {
						atomicInteger = new AtomicInteger(1);
						userInstallCountMap.put(test.getPackageName(), atomicInteger);
					} else {
						atomicInteger.incrementAndGet();
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		};

		IntStream.range(0, 20).forEach(x -> new Thread(newRunnable).start());

		Long index = userInstallCount / 100;
		for (int i = 0; i < 100; i++) {
			List<Test> tests = dslContext.fetch("select `packagename` from `user_install` limit " + i * index + "," + index).map(new RecordMapper<Record, Test>() {
				@Override
				public Test map(Record record) {
					Test test = new Test();
					test.setPackageName(record.get("packagename", String.class));
					return test;
				}
			});

			for (Test test : tests) {
				try {
					queue.put(test);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}

		while (queue.size() != 0) {
			System.out.println("waiting....");
			try {
				TimeUnit.SECONDS.sleep(3);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		userInstallCountMap.entrySet().parallelStream().forEach(x -> log.info(x.getKey() + "," + x.getValue().get()));
	}

	private void countLedai() {
		try {
			List<String> mobs = IOUtils.readLines(new FileInputStream(new File("/Users/Ray/Documents/3月8.csv")), StandardCharsets.UTF_8);

			Integer total = 0;
			for (String mob : mobs) {
				try {
					String[] aaaa = StringUtils.split(mob, ",");
					BirthdayUtils.calculateAgeFromIdCard(aaaa[0]);
				} catch (Exception e) {
					e.printStackTrace();
					total++;
				}
			}

			log.error("error total : {}", total);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void calculateBir() {
		try {
			List<String> mobs = IOUtils.readLines(new FileInputStream(new File("/Users/Ray/Documents/asdasd.txt")), StandardCharsets.UTF_8);

			Integer total = 0;
			for (String mob : mobs) {
				Long userId = dslContext.fetchOne("select `id`  from `comm_user` where `mobnum` ='" + mob + "'").into(Long.class);
				Long count = dslContext.fetchOne("select count(1)  from `loan_api_record` where `source_id`  = '213' and userid = " + userId).into(Long.class);
				if (count <= 0) {
					System.out.println(mob);
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void haha() {
		try {
			List<String> strs = IOUtils.readLines(new FileInputStream(new File("/Users/Ray/Documents/machin_learning_data/machin_learning_data_orginal.txt")),
					StandardCharsets.UTF_8);
			List<String> good = Lists.newArrayList();
			List<String> fail = Lists.newArrayList();
			for (String str : strs) {
				String[] objs = StringUtils.split(str, ",");
				String[] newObjs = new String[objs.length - 1];

				if (Integer.valueOf(objs[0].toString()) <= 0 && Integer.valueOf(objs[0].toString()) >= 100) {
					continue;
				}

				if (Integer.valueOf(objs[1].toString()) <= 0 && Integer.valueOf(objs[1].toString()) >= 50000) {
					continue;
				}

				if (Integer.valueOf(objs[3].toString()) <= 0 && Integer.valueOf(objs[3].toString()) >= 800) {
					continue;
				}

				if (Integer.valueOf(objs[4].toString()) <= 0 && Integer.valueOf(objs[4].toString()) >= 50000) {
					continue;
				}

				if (Integer.valueOf(objs[5].toString()) > 50) {
					continue;
				}

				newObjs[0] = objs[0];
				newObjs[1] = objs[1];
				newObjs[2] = objs[2];
				newObjs[3] = objs[3];
				newObjs[4] = objs[4];
				newObjs[5] = objs[5];
				if (Integer.valueOf(objs[7].toString()).equals(6)) {
					newObjs[6] = "1";
					good.add(StringUtils.join(newObjs, ","));
				} else {
					newObjs[6] = "0";
					fail.add(StringUtils.join(newObjs, ","));
				}
			}
			good.addAll(fail.subList(0, good.size()));
			Collections.shuffle(good);
			IOUtils.writeLines(good, IOUtils.LINE_SEPARATOR_UNIX, new FileOutputStream(new File("/Users/Ray/Documents/machin_learning_data/machin_learning_data_orginal1.txt")),
					StandardCharsets.UTF_8);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private List<Test> getFenleiData() {
		return dslContext.fetch(
				"select a.id,a.`idcard`,a.`amount`,a.`profession`,a.`income`,a.`sesame_point`,b.`channel`,b.`phoneid` from `loan_user_info` as a inner join `comm_user` as b on a.`userid`= b.id")
				.map(new RecordMapper<Record, Test>() {
					@Override
					public Test map(Record record) {
						Test test = new Test();
						test.setUserId(record.get("id", Long.class));
						test.setIdcard(record.get("idcard", String.class));
						test.setProfession(record.get("profession", String.class));
						test.setAmount(record.get("amount", Integer.class));
						test.setIncome(record.get("income", Integer.class));
						test.setChannel(record.get("channel", Integer.class));
						test.setPhoneId(record.get("phoneid", String.class));
						test.setSesamePoint(record.get("sesame_point", Integer.class));
						return test;
					}
				});
	}

	private void fenlei() {
		Runnable newRunnable = () -> {
			while (true) {
				try {
					Test test = queue.take();
					test.setAge(calculateAgeFromIdCard(test.getIdcard()));
					test.setAppCount(countAppIntersection(listPackageNameBy(test.getPhoneId())));

					UserLabel userLabel = userLabelRepository.findByUserId(test.getUserId());
					if (userLabel == null) {
						userLabel = new UserLabel();
					}

					userLabel.setAge(calculateAge(test.getIdcard()));
					userLabel.setAmount(calculateAmount(test.getAmount()));
					userLabel.setChannel(calculateChannel(test.getChannel()));
					userLabel.setIncome(calculateIncome(test.getIncome()));
					userLabel.setMultipleLoan(calculateAppCount(test.getAppCount()));
					userLabel.setProfession(calculateProfession(test.getProfession()));
					userLabel.setSesamePoint(calculateSesamPoint(test.getSesamePoint()));
					userLabel.setUserId(test.getUserId());

					userLabelRepository.save(userLabel);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		};

		IntStream.range(0, 20).forEach(x -> new Thread(newRunnable).start());

		List<Test> tests = getFenleiData();

		for (Test test : tests) {
			try {
				queue.put(test);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		while (queue.size() != 0) {
			System.out.println("waiting....");
			try {
				TimeUnit.SECONDS.sleep(3);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private Integer calculateAge(String idcard) {
		Integer age = calculateAgeFromIdCard(idcard);

		if (age == null) {
			return null;
		}

		if (age >= 18 && age <= 22) {
			return 1;
		} else if (age >= 23 && age <= 35) {
			return 2;
		} else if (age >= 36 && age <= 55) {
			return 3;
		} else if (age > 55 && age < 100) {
			return 4;
		} else {
			return null;
		}
	}

	private Integer calculateAmount(Integer amount) {
		if (amount == null) {
			return null;
		}

		if (amount < 2000) {
			return 1;
		} else if (amount >= 2000 && amount < 5000) {
			return 2;
		} else if (amount >= 5000 && amount < 8000) {
			return 3;
		} else if (amount >= 8000) {
			return 4;
		} else {
			return null;
		}
	}

	private Integer calculateProfession(String profession) {
		if (StringUtils.isBlank(profession)) {
			return null;
		}

		switch (profession) {
		case "上班族":
			return 1;
		case "个体户":
			return 2;
		case "自由职业":
			return 3;
		case "企业主":
			return 4;
		case "学生":
			return 5;
		case "其他":
			return 6;
		default:
			return null;
		}
	}

	private Integer calculateSesamPoint(Integer sesamePoint) {
		if (sesamePoint == null) {
			return null;
		}

		if (sesamePoint < 500) {
			return 1;
		} else if (sesamePoint >= 500 && sesamePoint < 550) {
			return 2;
		} else if (sesamePoint >= 550 && sesamePoint < 600) {
			return 3;
		} else if (sesamePoint >= 600) {
			return 4;
		} else {
			return null;
		}
	}

	private Integer calculateIncome(Integer income) {
		if (income == null) {
			return null;
		}
		if (income < 3000) {
			return 1;
		} else if (income >= 3000 && income < 6000) {
			return 2;
		} else if (income >= 6000 && income < 8000) {
			return 3;
		} else if (income >= 8000) {
			return 4;
		} else {
			return null;
		}
	}

	private Integer calculateAppCount(Integer appCount) {
		if (appCount == null) {
			return null;
		}

		if (appCount == 0) {
			return 1;
		} else if (appCount < 5) {
			return 2;
		} else if (appCount >= 5 && appCount <= 10) {
			return 3;
		} else if (appCount > 10) {
			return 4;
		} else {
			return null;
		}
	}

	private Integer calculateChannel(Integer channel) {
		if (channel == null) {
			return 5;
		}

		if (xinxiliuChannel.contains(channel)) {
			return 1;
		} else if (yingoyngfenfaChannel.contains(channel)) {
			return 2;
		} else if (duanxinChannel.contains(channel)) {
			return 3;
		} else if (tonghangChannel.contains(channel)) {
			return 4;
		} else {
			return 5;
		}
	}

	private void structure() {
		Runnable newRunnable = () -> {
			while (true) {
				try {
					Test test = queue.take();
					test.setAge(calculateAgeFromIdCard(test.getIdcard()));
					test.setAppCount(countAppIntersection(listPackageNameBy(test.getPhoneId())));
					test.setValidateFailCount(dslContext.fetch("select count(DISTINCT(source_id)) as count from loan_order where `userid` =  '" + test.getUserId()
							+ "' and status <> 6 union " + "select count(DISTINCT(source_id)) as count from loan_api_record where `userid` =  '" + test.getUserId()
							+ "' and (result <> 1 or first_union <> 1)").into(Long.class).parallelStream().mapToLong(x -> x).sum());
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		};

		IntStream.range(0, 20).forEach(x -> new Thread(newRunnable).start());

		List<Test> tests = getData();

		for (Test test : tests) {
			try {
				queue.put(test);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		while (queue.size() != 0) {
			System.out.println("waiting....");
			try {
				TimeUnit.SECONDS.sleep(3);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		try {
			IOUtils.writeLines(testToString(tests), IOUtils.LINE_SEPARATOR_UNIX,
					new FileOutputStream(new File("/Users/Ray/Documents/machin_learning_data/machin_learning_data_orginal23333.txt")), StandardCharsets.UTF_8);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private List<String> testToString(List<Test> tests) {
		List<String> strs = Lists.newArrayList();
		for (Test test : tests) {
			String age = safeConvert(test.getAge());
			String amount = safeConvert(test.getAmount());
			String profession = covertProfession(test.getProfession());
			String sesamePoint = safeConvert(test.getSesamePoint());
			String income = safeConvert(test.getIncome());
			String appCount = safeConvert(test.getAppCount());
			String channel = safeConvert(test.getChannel());
			String status = safeConvert(test.getStatus());
			String validateFail = safeConvert(test.getValidateFailCount());
			strs.add(StringUtils.join(new String[] { validateFail, age, amount, profession, sesamePoint, income, appCount, channel, status }, ","));
		}
		return strs;
	}

	private String covertProfession(String profession) {
		if (StringUtils.isBlank(profession)) {
			return "0";
		}
		switch (profession) {
		case "上班族":
			return "1";
		case "自由职业":
			return "2";
		case "个体户":
			return "3";
		case "企业主":
			return "4";
		case "其他":
			return "5";
		case "公务员":
			return "6";
		case "学生":
			return "7";
		default:
			return "0";
		}
	}

	private String safeConvert(Number lo) {
		if (lo == null) {
			return "";
		} else {
			return lo.toString();
		}
	}

	private List<Test> load() {

		return dslContext.fetch(
				"SELECT a.id,b.platform, a.product_id FROM comm_user AS a INNER JOIN loan_user_info AS b ON a.id = b.userid WHERE a.id IN (SELECT DISTINCT ( `user_id` ) FROM `pay_order` WHERE `order_status` IN ( - 1, 0 ) AND `create_time` >= '2018-01-20 00:00:00' AND `create_time` <= '2018-01-31 23:59:59' AND phone_num NOT IN ( SELECT DISTINCT ( phone_num ) FROM `pay_order` WHERE `order_status` IN ( 2 ) AND `create_time` >= '2018-01-20 00:00:00' AND `create_time` <= '2018-01-31 23:59:59' ))")
				.map(new RecordMapper<Record, Test>() {
					@Override
					public Test map(Record record) {
						Test test = new Test();
						test.setUserId(record.get("id", Long.class));
						test.setProductId(record.get("product_id", String.class));
						test.setPlatform(record.get("platform", String.class));
						return test;
					}
				});
	}

	// private List<Test> load() {
	// BufferedReader fileReader = null;
	// List<Test> tests = Lists.newArrayList();
	// try {
	// fileReader = new BufferedReader(new FileReader(new
	// File("/Users/Ray/Documents/pushtext.txt")));
	// String line = null;
	// while ((line = fileReader.readLine()) != null) {
	//
	// List<Test> sssss = dslContext
	// .fetch("SELECT a.mobnum, b.platform, a.product_id FROM comm_user AS a INNER
	// JOIN loan_user_info AS b ON a.id = b.userid WHERE a.mobnum = " + line)
	// .map(new RecordMapper<Record, Test>() {
	// @Override
	// public Test map(Record record) {
	// Test test = new Test();
	// test.setMobnum(record.get("mobnum", String.class));
	// test.setProductId(record.get("product_id", String.class));
	// test.setPlatform(record.get("platform", String.class));
	// return test;
	// }
	// });
	// tests.addAll(sssss);
	// }
	// } catch (Exception e) {
	// e.printStackTrace();
	// } finally {
	// try {
	// fileReader.close();
	// } catch (Exception e) {
	// e.printStackTrace();
	// }
	// }
	// return tests;
	// }

	private void push() {
		AtomicInteger count = new AtomicInteger(0);
		Runnable newRunnable = () -> {
			while (true) {
				try {
					Test test = queue.take();
					TestUtils.callServer(structureRequestJson(test.getUserId().toString(), test.getProductId(), test.getPlatform()),
							"http://39.108.183.162:14100/push_service/common?funid=999&rd=" + System.currentTimeMillis());
					count.incrementAndGet();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		};

		IntStream.range(0, 20).forEach(x -> new Thread(newRunnable).start());

		List<Test> tests = load();

		for (Test test : tests) {
			try {
				queue.put(test);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		while (queue.size() != 0) {
			System.out.println("waiting....");
			try {
				TimeUnit.SECONDS.sleep(3);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		log.info("pushed count : {}", count.get());
	}

	private List<Test> getPushUser() {
		return dslContext
				.fetch("SELECT a.`id` ,a.`product_id`  FROM comm_user as a inner join loan_user_info as b on b.userid = a.id " + "where b.amount >= 2000 and b.amount<= 8000 "
						+ "and b.profession in ('上班族','个体户','自由职业') " + "and b.sesame_point >= 600 " + "and b.income>=3000 and b.income <= 6000 "
						+ "and a.`id` not in (select d.`userid`  from loan_order as d where d.`source_id`  = '241') "
						+ "and a.`id` not in (select g.`userid` from loan_duotou_tmp as g) " + "order by a.`createtime` desc " + "limit 0,7000")
				.map(new RecordMapper<Record, Test>() {
					@Override
					public Test map(Record record) {
						Test test = new Test();
						test.setUserId(record.get("id", Long.class));
						test.setProductId(record.get("product_id", String.class));
						return test;
					}
				});
	}

	private JsonObject structureRequestJson(String userId, String productId, String platform) {
		JsonObject requestjson = new JsonObject();
		PushTemple template = new PushTemple();
		template.title = "清楚自己信用，撸对口子";
		template.content = "百分百下款";
		template.notifyType = 3;
		template.showType = 1;
		template.responseType = 2;
		template.icon = "https://img.tuanzidai.cn/push/iconWechatIMG86.png";

		JsonObject a = new JsonObject();
		a.addProperty("launch", "launch_vc_webView");
		JsonObject b = new JsonObject();
		b.addProperty("title", "黑名单查询");

		if ("ios".equalsIgnoreCase(platform)) {
			b.addProperty("htmlUrl", "http://tuanzidai.cn/share_service/redirect?from=6C2EWBGHYH");
		} else {
			b.addProperty("htmlUrl", "http://tuanzidai.cn/share_service/redirect?from=6C2EWBGHVD");
		}
		b.addProperty("withHead", 1);
		b.addProperty("showToolbar", 0);
		b.addProperty("clearTop", 0);
		b.addProperty("canBlockNetworkImg", 1);
		b.addProperty("reloadWhenLogin", 1);
		b.addProperty("takeOverBackPressed", 0);
		b.addProperty("callbackWhenResumeAndPause", 0);
		b.addProperty("showTitle", 1);
		b.addProperty("injectCss", 0);

		a.add("launchParams", b);
		template.responseParams = a;

		requestjson.addProperty("userid", userId);
		requestjson.addProperty("product_id", productId); // 值得买
		requestjson.addProperty("lockkey", "清楚自己信用，撸对口子! 百分百下款");
		requestjson.addProperty("platfrom", "IOS".equalsIgnoreCase(platform) ? 2 : 1);

		requestjson.add("msg", new Gson().toJsonTree(template));
		requestjson.addProperty("expiredate", "2017-11-18 12:15:00");
		JsonElement pheadjson = new Gson().toJsonTree(new Phead());
		requestjson.add("phead", pheadjson);
		return requestjson;
	}

	private void test() {
		Runnable newRunnable = () -> {
			try {
				while (true) {
					Test test = queue.take();
					Long validateFailCount = dslContext.fetch("select count(DISTINCT(source_id)) as count from loan_order where `userid` =  '" + test.getUserId()
							+ "' and status <> 6 union " + "select count(DISTINCT(source_id)) as count from loan_api_record where `userid` =  '" + test.getUserId()
							+ "' and (result <> 1 or first_union <> 1)").into(Long.class).parallelStream().mapToLong(x -> x).sum();
					test.setApplySuccessCount(validateFailCount);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		};

		IntStream.range(0, 20).forEach(x -> new Thread(newRunnable).start());

		List<Test> tests = get();

		for (Test test : tests) {
			try {
				queue.put(test);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		while (queue.size() != 0) {
			System.out.println("waiting....");
			try {
				TimeUnit.SECONDS.sleep(3);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		Map<Integer, List<Test>> countMap = new HashMap<>();

		for (Test test : tests) {
			if (test.getApplySuccessCount() >= 5L) {
				List<Test> testList = countMap.get(0);
				if (testList == null) {
					testList = Lists.newArrayList();
				}
				testList.add(test);
				countMap.put(0, testList);
				continue;
			}

			if (test.getApplySuccessCount() >= 3L && test.getApplySuccessCount() <= 4L) {
				List<Test> testList = countMap.get(1);
				if (testList == null) {
					testList = Lists.newArrayList();
				}
				testList.add(test);
				countMap.put(1, testList);
				continue;
			}

			if (test.getApplySuccessCount() >= 1L && test.getApplySuccessCount() <= 2L) {
				List<Test> testList = countMap.get(2);
				if (testList == null) {
					testList = Lists.newArrayList();
				}
				testList.add(test);
				countMap.put(2, testList);
				continue;
			}

			if (test.getApplySuccessCount().equals(0L)) {
				List<Test> testList = countMap.get(3);
				if (testList == null) {
					testList = Lists.newArrayList();
				}
				testList.add(test);
				countMap.put(3, testList);
				continue;
			}
		}

		for (Map.Entry<Integer, List<Test>> entry : countMap.entrySet()) {
			Map<Integer, Long> map = entry.getValue().parallelStream().collect(Collectors.groupingBy(Test::getStatus, Collectors.counting()));
			map.entrySet().parallelStream().forEach(x -> log.info("key : " + entry.getKey() + "status : " + x.getKey() + " count : " + x.getValue()));
		}
	}

	protected Set<String> loanPhones = Sets.newHashSet("10109188", "400-8133-233", "4009991028", "400-900-8880", "4001728928", "010-56592326");

	protected Set<Integer> xinxiliuChannel = Sets.newHashSet(120288, 120289, 120290, 120291, 120292, 120293, 120294, 120295, 120296, 120297, 120298, 120299, 120300, 120301, 120302,
			120303, 120304, 120305, 120306, 120307, 120358, 120359, 120360, 120361, 120362, 120363, 120364, 120365, 120366, 120367, 120368, 120369, 120370, 120371, 120372, 120373,
			120374, 120375, 120376, 120377, 120308, 120309, 120310, 120311, 120312, 120313, 120314, 120315, 120316, 120317, 120319, 120320, 120321, 120322, 120323, 120324, 120325,
			120326, 120327, 120383, 120384, 120385, 120386, 120387, 120405, 120406, 120407, 120408, 120409, 120410, 120411, 120412, 120413, 120414, 120415, 120416, 120417, 120418,
			120419, 120440, 120441, 120442, 120443, 120444, 120464, 120472, 120206, 120207, 120208, 120209, 120210, 120139, 120140, 120141, 120142, 120143, 120144, 120145, 120146,
			120147, 120148, 120149, 120150, 120151, 120152, 120153, 120154, 120155, 120156, 120157, 120158, 120344, 120430, 120431, 120432, 120433, 120434, 120435, 120436, 120437,
			120438, 120439, 120445, 120446, 120447, 120448, 120449, 120465, 120466, 120030, 120174, 120175, 120176, 120177, 120178, 120226, 120227, 120228, 120229, 120230, 120231,
			120232, 120233, 120234, 120235, 120240, 120241, 120242, 120243, 120244, 120184, 120185, 120186, 120187, 120188, 120189, 120190, 120191, 120192, 120193, 120194, 120195,
			120196, 120197, 120198, 120199, 120200, 120201, 120202, 120203, 120214, 120215, 120216, 120217, 120218, 120219, 120220, 120221, 120222, 120223, 130016, 130017, 130018,
			130019, 130020, 120238, 120239, 120264, 120328, 120329, 120330, 120331, 120332, 120333, 120334, 120335, 120336, 120337, 120348, 120349, 120350, 120351, 120352, 120353,
			120354, 120355, 120356, 120357, 120423, 120424, 120425, 120426, 120427, 120461, 120462, 120463, 120484, 120488, 120029, 120093, 120040, 120390, 120392, 120401, 120402,
			120403, 120404, 120405, 120031, 120033, 120034, 120035, 120036, 120037, 120038, 120039, 120041, 120042, 120043, 120044, 120045, 120046, 120047, 120048, 120051, 120054,
			120055, 120056, 120057, 120058, 120059, 120071, 120072, 120073, 120074, 120075, 120076, 120077, 120078, 120079, 120080, 120081, 120082, 120083, 120096, 120097, 120098,
			120099, 120100, 120101, 120102, 120103, 120105, 120104, 120106, 120107, 120108, 120109, 120110, 120111, 120112, 120113, 120114, 120115, 120116, 120117, 120120, 120121,
			120122, 120119, 120137, 120138, 120123, 120124, 120125, 120126, 120127, 120128, 120129, 120130, 120131, 120159, 120160, 120161, 120162, 120163, 120164, 120165, 120166,
			120167, 120168, 120169, 120170, 120171, 120172, 120173, 120254, 120255, 120256, 120257, 120258, 120259, 120260, 120261, 120262, 120263, 120269, 120270, 120271, 120272,
			120273, 120274, 120275, 120276, 120277, 120286, 120344, 120458);

	protected Set<Integer> tonghangChannel = Sets.newHashSet(120019, 120023, 120026, 120024, 120099, 120027, 120025, 120028, 120032, 120049, 120050, 120052, 120053, 120060, 120061,
			120063, 120064, 120065, 120067, 120068, 120069, 120084, 120086, 120087, 120088, 120089, 120090, 120092, 120094, 120095, 120118, 120132, 120133, 120134, 120135, 120136,
			120179, 120180, 120181, 120182, 120183, 120204, 120205, 120211, 120212, 120213, 140002, 140001, 140003, 140004, 140005, 140006, 140007, 140008, 140009, 140010, 140011,
			140012, 140013, 140014, 140015, 140016, 140017, 140018, 140019, 140020, 140021, 140022, 140023, 140024, 140025, 140026, 140027, 140028, 140029, 140030, 120224, 120236,
			120245, 120246, 120247, 120248, 120249, 120250, 120251, 120252, 120253, 120265, 120278, 120284, 120285, 120287, 120318, 120338, 120339, 120340, 120342, 120343, 120345,
			120346, 120347, 120379, 120380, 120381, 120382, 120393, 120394, 120395, 120396, 120398, 120400, 120420, 120429, 120451, 120452, 120454, 120459, 120460, 120469, 120475);

	protected Set<Integer> yingoyngfenfaChannel = Sets.newHashSet(120001, 120002, 120003, 120004, 120005, 120006, 120007, 120008, 120009, 120010, 120011, 120012, 120013, 120014,
			120015, 120016, 120017, 120018, 120020, 120021, 120000, 120022, 100, 130001, 130002, 130000, 130003, 130004, 130005, 130006, 130007, 130008, 130009, 130010, 130011,
			130012, 130013, 130015, 120066, 120070, 120397, 120428, 120450);

	protected Set<Integer> duanxinChannel = Sets.newHashSet(120085, 120091, 120225, 120237, 120279, 120280, 120281, 120282, 120283, 120341, 120388, 120399, 120421, 120453, 120455,
			120456, 120457, 120467, 120468, 120470, 120473, 120476, 120477, 120478, 120479, 120471, 120480, 120481, 120487, 120489, 120490, 120491, 120492, 120500);

	private List<Test> get() {
		return dslContext.fetch(
				"select distinct(b.id), b.phoneid,c.status as status from `loan_user_info` as a inner join `comm_user` as b on a.`userid`= b.`id` inner join `loan_order` as c on c.`userid`= b.`id` and c.`status` in(2, 3, 4, 6)")
				.map(new RecordMapper<Record, Test>() {
					@Override
					public Test map(Record record) {
						Test test = new Test();
						test.setUserId(record.get("id", Long.class));
						test.setPhoneId(record.get("phoneid", String.class));
						test.setStatus(record.get("status", Integer.class));
						return test;
					}
				});
	}

	private List<Test> getData() {
		return dslContext.fetch(
				"select b.id as userid,a.`idcard`,a.`amount`,a.`profession`,a.`income`,a.`sesame_point`,b.`channel`,b.`phoneid`,c.`status`from `loan_user_info` as a inner join `comm_user` as b on a.`userid`= b.id inner join `loan_order` as c on a.`userid`= c.`userid` where c.`source_id`= '241' and c.status <> 9")
				.map(new RecordMapper<Record, Test>() {
					@Override
					public Test map(Record record) {
						Test test = new Test();
						test.setUserId(record.get("userid", Long.class));
						test.setIdcard(record.get("idcard", String.class));
						test.setProfession(record.get("profession", String.class));
						test.setAmount(record.get("amount", Integer.class));
						test.setIncome(record.get("income", Integer.class));
						test.setChannel(record.get("channel", Integer.class));
						test.setPhoneId(record.get("phoneid", String.class));
						test.setSesamePoint(record.get("sesame_point", Integer.class));
						test.setStatus(record.get("status", Integer.class));
						return test;
					}
				});
	}

	@Data
	private static final class Test {
		private Long userId;

		private String productId;

		private Date createTime;

		private Date applyTime;

		private String province;
		private Date payTime;

		private String idcard;

		private Integer income;

		private String sex;

		private Integer age;

		private String credit;

		private Integer appCount;

		private Integer amount;

		private Long apiAndUnionCount;

		private Long validateFailCount;

		private Long applySuccessCount;

		private Integer status;

		private Integer channel;

		private String profession;

		private Integer sesamePoint;

		private String packageName;

		private String phoneId;

		private String phoneBook;

		private String callRecord;

		private String platform;

		private String mobnum;

	}

	private static final class PushTemple {

		public String title = "";
		public String content = "";
		public String server_id = "";
		public Integer notifyType;
		public Integer responseType;
		public String icon;
		public Integer showType;
		public JsonObject responseParams;

	}

	protected Set<String> loanAppPackageNames = Sets.newHashSet("com.lingyue.YqdAndroid", "com.cashbus.android.swhj", "com.kdlc.mcc", "com.aldb.android", "com.innext.xjx",
			"com.youtuker.xjzx", "com.xiangbudao.xjjd", "com.flash.cash", "cn.cash365.android", "com.xianjin.main", "com.qianyinjinrong.app12a", "com.wdzj.borrowmoney",
			"apps.cashboxs", "cn.cashfree.loan", "com.duorong.xianjingdai", "com.app.xianjinzhijia.main", "com.geo.cashcow", "com.zhouji.luotuo", "com.app.finance",
			"cn.mifengkong.loan", "cn.guorun.mt", "cn.mifengkong.cps", "com.Michael.xianjinka", "com.xinhe.cashloan", "com.beihui.aixinqqqq", "cn.mifengkong.huaxiaapp",
			"com.gcs.wang.loan", "com.innext.ztc", "com.ty.firstlywallet", "com.ahxbapp.xjxx", "com.fengniao.action", "com.rongba.xianjindaikuan", "com.duorong.xianjingdai",
			"com.loan.petty.pettyloan", "com.xjqb", "com.chutong.casheasy.app", "com.wildcode.cashtaxi", "cn.shishidai.H5D5071E4", "com.chenggutek.cashpocket",
			"com.jiafenqi.loanmanager.sjjk", "com.bangnidai.app", "com.htouhui.pdl.xianjingou", "com.v358093267.qgm", "com.youtuker.zdb", "com.xianjinxia",
			"com.niuwa.niubei.android", "com.kdlc.mj2", "com.wildcode.kuaika", "com.t358093267.kry", "com.xinhe.cashloan", "com.cn.moonlightbluecard", "com.junzhuo.xianjinbang",
			"com.yongqianbei.loan", "com.huocheche", "com.shuqu.microcredit", "com.mjoys.finaceapp", "com.yongqianbao.borrowmoney", "com.ahxbapp.xjsd", "com.qhhy.xjjq",
			"com.treefinance.supermarket", "com.kuxuan.money", "com.mjb.moneymanager.jkb", "com.a358093267.vvo", "com.xinhe.cashloan", "com.xinyongfei.xyf", "com.qsq.loankaka",
			"com.jiafenqi.loanmanager.xjdkjq", "com.ahxbapp.xjyijie", "com.huaxin.jieqianhua339", "jinfujt.com.xjmm", "com.example.administrator.immediatecash",
			"com.example.pro_phonexjdk", "com.aloestec.cash.xigua", "com.innext.cash", "com.app.feisuda", "com.alfl.www", "com.treefinance.supermarket", "com.sogou.androidtool",
			"com.mjb.moneymanager.likefq", "com.liquan.xjxjq", "com.canfu.kdgeb", "com.xybt.xjbkfuli", "com.kdlc.mj2", "com.zlkj.xianjinzaixiandk",
			"com.mai400.android.xianjinsujie", "com.app.xianjincf", "com.martian.alihb", "cn.jiujiudai.wubadaikuan", "com.zhangzhong.loan.xjdai", "com.xncredit.pad",
			"com.qiantong.daikuan", "com.rst.cashloan", "com.woyaoxianjin", "cn.daoan.xianjinloan", "com.rongba.xianjin", "com.yiqiwork.xidai", "com.rzj.jsjk",
			"com.guwen.app.xjxd", "com.htffund.mobile.ec.ui", "com.ahxbapp.xjjsd", "com.sirbaylor.speed", "com.xjdk.credit", "com.z929178222.jsdxjd405", "com.qicash.cash74",
			"com.jq.credit", "com.ahxbapp.xjqiandai", "com.dk.xjjd", "com.yuntu.jkzjzyb", "com.qiantu.sdzx", "cn.paidan.jiebei", "www.odin.com.xjfxd", "com.qijiukeji.xjkd",
			"com.app.xianjinjisuda", "com.r358093267.emh", "com.xdq.quickloan", "com.jiafenqi.loanmanager.xykjq", "com.ppdai.loan.buy", "com.daixiaoqian", "com.qhhy.xjjq",
			"com.huaxin.jieqianhua82", "com.qijiukeji.xjkd", "com.ahxbapp.xjshidai", "com.c358093267.euy", "com.zhangzhong.borrow.xjjq", "com.loancommon", "com.ahxbapp.xjkd",
			"com.huaxin.jieqianhua70", "com.cbb19900208.wbp", "com.huaxin.jieqianhua228", "com.a358093267.vvo", "com.dai58.miaojiexianjin", "com.modai.xianjinmoka", "com.xfqz.xjd",
			"com.wta.NewCloudApp.jiuwei80432e", "com.xianjinxiaoge.main", "cn.jiujiudai.tiantianjiekuan", "com.caimao.cashloan.xjjk", "app.xuezhongdai.com.xzjrapp",
			"com.example.moneystagesyingyongbao", "cn.cashfree.xianjindai", "com.baas.tbk676", "com.dkw.credit", "com.yuyong.jiekuanbao", "com.Michael.xianjinka",
			"com.quickCashLoan", "com.qijiukeji.xjkd", "com.suishixianjin.company", "com.thans.xianjinyidai", "cn.jiujiudai.jiedaiba", "com.yuntu.jkzjflb", "com.baidu.appsearch",
			"com.example.pro_phones_jtb", "com.sogou.androidtool", "com.zlkj.xianjinbaikaw", "com.ahxbapp.loan", "com.huaxin.jieqianhua225", "com.xncredit.loan",
			"com.mjoys.finacestore", "com.yunshuo.kdxj", "com.sirbaylor.shandian", "com.www.cn.shoujixianjindai", "com.qhhy.xjjq", "com.innext.xjx", "net.zombie_sama.xianjinbashi",
			"com.mjb.moneymanager.creditdk", "com.sogou.androidtool", "com.baidu.appsearch", "bill.zts.com.mofaxianjin", "com.sogou.androidtool", "com.caimao.cashloan.jkkz",
			"com.huanyue.qingsonglend.welfare", "com.yongqianbao.borrowmoney", "com.z929178222.xjdkxm202", "com.treasureapp.xianjinbaikadai", "com.gree.xianjzxd",
			"com.baidu.appsearch", "com.jinqianbao.xjbk", "com.baidu.appsearch", "com.liquan.xjxlxb", "com.feilai.shan", "cn.mifengkong.huaxiaapp", "com.xjjk.credit",
			"com.xjdx.xianjindaxia50228", "cn.mirong.loan1", "com.xiaoiyunque.xiaoyunque", "com.cashapp.xjjb", "com.xiangjinjiayouzhan.main", "com.zjjyy.wang.loan",
			"com.xjhuahua.loan", "com.example.apple.xianjinxia", "com.weirong.wrjkfb", "com.xiyoukeji.xinhuahua", "com.wczr.speedloan", "com.z929178222.msd421",
			"mobi.woyaocashloan", "com.liquan.xjxyq", "com.zhangzhong.loan.xjdai", "com.qianwanwan.jinjiebao", "com.mjb.moneymanager.jsdk", "com.zlkj.xianjinkuaidaijkb",
			"com.jisujiedaihaojieqianzhushou.money", "com.soodai.LGS", "com.mjb.moneymanager.jsdk", "cn.daoan.qianbao", "com.www.cn.xianjinwanka", "com.dk.xjjkk",
			"com.zlkj.xianjinxia", "com.jiafenqi.loanmanager.xjqb", "com.nfsq.xianjzxqb", "com.qmdk.xjdkjd", "com.sunmeng.daxiangxinyongandroid", "com.feilu.flashloan",
			"com.zhijie.jisuloan", "com.hzzc.investor", "cn.creditcrest.toloan", "com.z929178222.xjw", "com.jd360.xjm", "com.gongyinruixin", "com.caimao.cashloan.dkx",
			"com.baidu.appsearch", "io.dcloud.H5E886092", "com.sogou.androidtool", "com.baidu.appsearch", "com.yingjidai", "com.yame.caidai22", "www.odin.com.xjbkb",
			"com.baidu.appsearch", "com.mjb.moneymanager.xjqb", "com.alfl.www.borrowSuperman", "com.home5566.cash", "com.baidu.appsearch", "com.example.xianjinjinkayinyongbao",
			"com.weirong.wrdkfb", "com.xianjinchaoren", "com.ionicframework.zwxjmf813242", "com.zzcm.fastborrow", "com.wzy.xiaoshimindaikuanzhushou", "com.rzj.dkds",
			"com.cn.moonlightbluecard", "com.qhhy.xjjq", "com.baidu.appsearch", "com.yuntu.daikuanbei", "com.sogou.androidtool", "com.mjb.moneymanager.xjdkjq", "com.dkhelpercre",
			"com.isq.loan_market.loan_market", "com.sogou.androidtool", "bill.zts.com.mofaxianjin", "com.gree.chaorxjd", "com.jq.sjxj", "com.mjb.moneymanager.xjsdjk",
			"com.treasureapp.geinihuaxianjinjiekuan", "com.huaxin.jieqianhua167", "com.juhe.cash", "com.haodai.loanpro", "com.sogou.androidtool", "com.rong3600.xianjindai",
			"com.zlkj.jinqiannainiu", "com.mjb.moneymanager.kqdk", "com.loancommon", "com.home5566.cash", "com.huaxin.jieqianhua230", "com.yaqi.mj.xianjin",
			"com.huanyue.qingsonglend.official", "com.xiangjinjiayouzhan.main", "com.mjb.moneymanager.xjqbsdjk", "com.huaxin.jieqianhua100", "com.xncredit.loan",
			"com.baidu.appsearch", "com.example.xjnn52", "com.sunmeng.xianjinzhouzhuan", "com.example.xianjinxiajisubanyingyongbao", "com.apple.xianjinniu",
			"com.yuanao.xjdfqjkX23", "com.x50794jjk.xianjinjiekuan", "com.loan.xiaomayixianjindai", "com.xjqb", "com.shhxzq.xjb", "com.huanyue.freelend.lendlend",
			"com.huarongxjb.dzh", "com.dizs.xjh", "com.x50791jqz.xianjinqianzhan", "com.huangguan.hgmoney", "com.ceylxj4", "cn.mifengkong.loan2", "com.example.xjxkz",
			"com.xiecc.xianjinshoujidai", "com.sogou.androidtool", "com.manyishuo.comjxxjqp1", "com.qhsd.xjd", "com.xft.loan", "com.xiangjinjiayouzhan.main", "com.haopeng.xianjin",
			"com.mjb.moneymanager.xjb", "com.dkqb.credit", "io.thans.xianjinyidai", "com.zhangzhong.borrow.hbdkfq", "com.xjqb", "com.wta.NewCloudApp.jiuwei156063",
			"com.sunmeng.androidmashangdai", "com.zzcm.fastborrow", "com.syj.jsxjj", "com.gjj51.housingfund.beijing", "com.isq.loan_market.loan_market", "com.yuanao.jqxjdkrjX26",
			"com.mjb.moneymanager.xjfqjq", "com.mjb.moneymanager.jqgnh", "com.loan.platform", "com.jqb.credit", "com.mjb.moneymanager.jsjq", "com.mjb.moneymanager.jkb",
			"com.a726787295.gamecash", "com.xinhe.xianjinchaoren", "io.dcloud.H50BDC243", "com.sooying.daikuanbao", "com.kuozheng.demo1", "com.example.apple.xianjinbashi",
			"com.niuwa.niubei.android", "com.choujiang500", "com.lvzuanshd", "com.yuntu.jkzjjsb", "com.baidu.appsearch", "c.b.h", "com.home5566.cash",
			"com.sunmeng.shanyinxianjindaiandroid", "com.loan.wannengdai", "com.huaxin.jieqianhua95", "com.yuanao.xjjqkX16", "com.z929178222.xjdhw206", "com.bangnidai.app",
			"com.qmdk.xjdkjd", "com.apple.xianjinniu", "com.yuntu.daikuanbei", "com.weirong.wrjkfb", "com.crfchina.market", "com.jfbank.wanka", "com.yuecai.mochouhua",
			"com.lsxinyong.www", "com.sinaif.credit", "com.shudu.chaoshi", "com.jiuji51.android.microloan", "com.luke.chitupay", "com.bank9f.staging", "com.xjkc", "com.xssd.xsph",
			"com.vcredit.j1000", "com.chutong.casheasy.app", "com.heige.mxlife", "com.huaxin.jieqianhua548", "com.example.yixinyingyongbao", "com.llq.linglingqihd",
			"com.xinhe.hengchang", "com.bestpay.creditcard");

	protected Integer countAppIntersection(Set<String> packageNames) {
		return packageNames.parallelStream().filter(x -> loanAppPackageNames.contains(x)).collect(Collectors.counting()).intValue();
	}

	public static final Integer calculateAgeFromBirthday(String birthday) {
		try {
			if (StringUtils.isBlank(birthday)) {
				return null;
			}
			return getAge(parse(birthday));
		} catch (Exception e) {
			LogService.error(e);
			return null;
		}
	}

	public Set<String> listPackageNameBy(String phoneId) {
		return dslContext.fetch("SELECT packagesrc FROM user_install WHERE phoneid = '" + phoneId + "'").intoSet("packagesrc", String.class);
	}

	public static final Integer calculateAgeFromIdCard(String idCard) {
		try {
			if (StringUtils.isBlank(idCard)) {
				return null;
			}
			String birthdayYear = idCard.substring(6, 10);
			String birthdayMonth = idCard.substring(10, 12);
			String birthdayDay = idCard.substring(12, 14);
			return calculateAgeFromBirthday(StringUtils.join(new String[] { birthdayYear, birthdayMonth, birthdayDay }, "-"));
		} catch (Exception e) {
			LogService.error(e);
			return null;
		}
	}

	private static final Date parse(String strDate) throws ParseException {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		return sdf.parse(strDate);
	}

	private static final Integer getAge(Date birthDay) throws Exception {
		Calendar cal = Calendar.getInstance();

		if (cal.before(birthDay)) {
			throw new IllegalArgumentException("The birthDay is before Now.It's unbelievable!");
		}
		int yearNow = cal.get(Calendar.YEAR);
		int monthNow = cal.get(Calendar.MONTH);
		int dayOfMonthNow = cal.get(Calendar.DAY_OF_MONTH);
		cal.setTime(birthDay);

		int yearBirth = cal.get(Calendar.YEAR);
		int monthBirth = cal.get(Calendar.MONTH);
		int dayOfMonthBirth = cal.get(Calendar.DAY_OF_MONTH);

		int age = yearNow - yearBirth;

		if (monthNow <= monthBirth) {
			if (monthNow == monthBirth) {
				if (dayOfMonthNow < dayOfMonthBirth)
					age--;
			} else {
				age--;
			}
		}
		return age;
	}
}
